package generator

import generator.common.{ExcelWriter, FreeMarker}
import generator.entity.{RowInfo, testInfo}
import generator.util.{captureName, dataBaseConnect}

import java.io.File
import scala.collection.mutable
import scala.collection.mutable.ListBuffer
import scala.io.Source
import scala.util.matching.Regex

object GeneratorSqlFromFile {
  var fromTableName = ""
  def getFieldFromSetFunc(str: String): String = {
    var funcName = str.substring(3)
    val regex = new Regex("([A-Z])")
    regex.replaceAllIn(funcName,matcher=>{
      "_"+matcher.group(1)
    }).substring(1)
  }

  def getFromMain(tableName: String):mutable.HashMap[String,String] = {
    val map:mutable.HashMap[String,String] = mutable.HashMap()
    val mainFile = new File("E:\\program\\scala\\StreamTransform\\src\\main\\java\\com\\kafka\\"+tableName.replaceAll("_","").toLowerCase()+"\\"+captureName.captureName(tableName,true)+"_Main.java")
    val regex = new Regex("d_bean.set([0-9a-zA-Z_]+)\\(j_bean.getAfter\\(\\).get[a-zA-Z]+\\(\"([a-zA-Z_]+)\".toUpperCase\\(\\)\\)\\);")
    val content = Source.fromFile(mainFile).mkString.replace("\r","")
    val tableNameRegex = new Regex("//topic:([a-zA-Z_]+)")
    fromTableName = tableNameRegex.findFirstMatchIn(content).get.group(1)
    regex.findAllMatchIn(content).foreach(matcher=>{
      map.put(matcher.group(1),matcher.group(2))
    })
    map
  }

  def main(args: Array[String]): Unit = {
    //    dataBaseConnect.connect("c_cons_prc")
    val marker = new FreeMarker()
    val empty = "\\s*\n*\\s*"

    var regex = new Regex("String sql1 = ([^;]+);")
    val regexDelete = new Regex("String sql2 = ([^;]+);") //"FC_CONS_GC_RELA","R_CP_MP_RELA","c_elec_addr","c_meter","C_CONS_CONTACT_RELA",
    val pstmRegex = new Regex(s"pstmt1.set[A-Za-z0-9_]+\\(([0-9]+),\\s*(\\(Date\\))?\\s*v.get([0-9a-zA-Z_]+)\\(\\)\\);")
    val tableList = List[String]("D_METER","D_LC_EQUIP","FC_GC","c_meter_read","c_meter_mp_rela","FC_GC_TYPE_CODE","g_TG","R_CP_CONS_RELA","C_MP","R_TMNL_RUN","R_CP","C_CONS","C_CONS_PRC","G_LINE","G_LINE_RELA","G_LINE_TG_RELA","g_subs","g_subs_line_rela","c_mp_it_rela","C_CONTACT","C_CUST","c_it_run","c_ps","g_tran","C_ELEC_ADDR_DET")
    //"D_METER","D_LC_EQUIP","FC_GC","c_meter_read","c_meter_mp_rela","FC_GC_TYPE_CODE","g_TG","R_CP_CONS_RELA","C_MP","R_TMNL_RUN","R_CP","C_CONS","C_CONS_PRC","G_LINE","G_LINE_RELA","G_LINE_TG_RELA","g_subs","g_subs_line_rela","c_mp_it_rela","C_CONTACT","C_CUST","c_it_run","c_ps","g_tran","C_ELEC_ADDR_DET"
    var textFile:File = null

    for (tableName <- tableList) {
      //println(tableName)
      textFile = new File("E:\\program\\scala\\StreamTransform\\src\\main\\java\\com\\kafka\\"+tableName.replaceAll("_","").toLowerCase()+"\\"+captureName.captureName(tableName,true)+"SinkFunc.java")
      val content = Source.fromFile(textFile).mkString.replace("\r","")
      val sql1 = regex.findFirstMatchIn(content).get.group(1).replace("\r","")
      val sql2 = regexDelete.findFirstMatchIn(content).get.group(1)
      val pstmlist = pstmRegex.findAllMatchIn(content).map(matcher=>(matcher.group(1),matcher.group(3))).toList

      val mergeRegex = new Regex("\"([^\"]+)\"")
      val mergeSql = mergeRegex.findAllMatchIn(sql1).map(_.group(1)).reduceLeft(_ + _).toUpperCase().replace("SELECT","@").replace("FROM","@").replaceAll("WHEN","#").replace("VALUES","&")
      val deleteSql = mergeRegex.findFirstMatchIn(sql2).get.group(1).toUpperCase()
      val empty = "\\s*"
      val name = "[A-Z_]+"
      val fieldsRegex = new Regex(s"[^@]+@${empty}([^@]+)${empty}@")
      val onRegex = new Regex(s"ON${empty}\\(${empty}T\\.(${name})${empty}=${empty}S\\.(${name})\\)")
      val setRegex = new Regex(s"UPDATE${empty}SET${empty}([^#]+)#")
      val insertFieldRegex = new Regex(s"INSERT${empty}([^&]+)&")
      val insertValueRegex = new Regex(s"&([^&]+)")

      val map = getFromMain(tableName)
      var fieldSql = fieldsRegex.findFirstMatchIn(mergeSql).get.group(1).replaceAll(" AS ","@")
      val replaceRegex = new Regex("\\?")
      var index = 0
      var emptynum = 0
      if (tableName == "R_TMNL_RUN") {
        println()
      }
      fieldSql = replaceRegex.replaceAllIn(fieldSql,matcher=>{
        if (index-emptynum > pstmlist.length-1 || pstmlist.apply(index-emptynum)._1.toInt!=index+1) {
          emptynum = emptynum + 1
          index = index + 1
          "?"
        }
        else {
          val str = pstmlist.apply(index-emptynum)._2
          index = index + 1
          map.get(str).get
        }

      })

      //val onField = onRegex.findFirstMatchIn(mergeSql).get.group(1)
      //val onValue = onRegex.findFirstMatchIn(mergeSql).get.group(2)
      val setSql = setRegex.findFirstMatchIn(mergeSql).get.group(1)
      val insertFieldSql = insertFieldRegex.findFirstMatchIn(mergeSql).get.group(1)
      val insertValueSql = insertValueRegex.findFirstMatchIn(mergeSql).get.group(1)

      val fieldRegex = new Regex("([^@]+)@([^@]+)")
      val fieldsRegex2 = new Regex("([^@]+@[^,]+,?)")
      val list:ListBuffer[RowInfo] = ListBuffer()
      val fieldList = fieldsRegex2.findAllMatchIn(fieldSql).map(_.matched).map[RowInfo](sql=>{
        val matcher = fieldRegex.findFirstMatchIn(sql).get
//        println(matcher.group(1))
        new RowInfo(null,null,null,matcher.group(1).trim,null,matcher.group(2).replace(",","").trim)
      }).toList
      val insertFields = insertFieldSql.replace("(","").replace(")","").split(",").map(_.trim)
      val inserValues = insertValueSql.replace("(", "").replace(")", "").split(",").map(_.trim)

      for (i <- 0 until(insertFields.length)) {
        for (rowinfo<-fieldList) {
          if (rowinfo.cj2_field.equals(inserValues.apply(i).replace("S.","").trim)) {
            list.append(new RowInfo(tableName,null,insertFields.apply(i),rowinfo.fromExp,null,rowinfo.cj2_field))
          }
        }
      }

      println("- "+tableName+" " + fromTableName)
      println(s"select count(1) from cj1.${tableName}")
      println(s"select count(1) from ${tableName}@DBL_75")
//      for (i <- 0 until(list.length)) {
//
//        val rowInfo = list.apply(i)
//
//      }






//      val writer = ExcelWriter()
//      val writeFile = "datas/对应关系2.xlsx"
//      writer.openXSSFWorkbook(writeFile)
//
//      var rowId = 0;
//      writer.createSheet(0, tableName, writeFile)
//
//      for (i <- 0 until(list.length)) {
//
//        val rowInfo = list.apply(i)
////        println(rowInfo.fromExp)
//        writer.writerToXSSFSheet(rowInfo.cj1_tableName, writeFile, rowInfo.fromTableField, 0, rowId, 0)
//        writer.writerToXSSFSheet(rowInfo.cj1_tableName, writeFile, rowInfo.fromExp, 0, rowId, 1)
//        rowId = rowId + 1;
//      }
//      writer.write(writeFile)
    }
  }
}
